<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*,java.io.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
			<p>MS Student Remaining Degree Requirements</p>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						CachedRowSet cachedQuery = new CachedRowSetImpl();
						Map<String, Integer> unitRequirements = null;
						Map<String, Float> gradeRequirements = null;
						ArrayList<String> completedConcentration = null;
						Map<String, Boolean> avgGradeSatisfactory = null;
						
						Map<String,LinkedList<String>> courseRemaining = null;
						//System.out.println("----------------------------------");
						if (action != null && action.equals("submit")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							//*********************code below deals with completed concentration **************************** //
							//System.out.println("----------------------------------");
							// find out the concentration requirements
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT * FROM concentration WHERE requirement_id = ?");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id"))); 
							ResultSet rsConcentration = pstmt.executeQuery();
							CachedRowSet cachedConcentration = new CachedRowSetImpl();
							cachedConcentration.populate(rsConcentration);
							//System.out.println("----------------------------------");
							unitRequirements = new HashMap<String, Integer>();
							gradeRequirements = new HashMap<String, Float>();
							while(cachedConcentration.next()){   //store min_units and min_avg_grade into hashtable
								unitRequirements.put(cachedConcentration.getString("topic"), cachedConcentration.getInt("min_units")); 
								pstmt = conn.prepareStatement("SELECT number_grade FROM grade_conversion WHERE letter_grade = ?");
								pstmt.setString(1, cachedConcentration.getString("min_avg_grade")); 
								ResultSet rsGradeConversion = pstmt.executeQuery();
								if(rsGradeConversion.next())
									gradeRequirements.put(cachedConcentration.getString("topic"), rsGradeConversion.getFloat("number_grade"));
							}
							//System.out.println("----------------------------------");
							// find out the sum of units of each concentration topic taken from the student
							pstmt = conn.prepareStatement("SELECT topic, sum(units) AS sum FROM enroll, section, class, concentration, concentration_course " +
									 "WHERE student_id = ? AND concentration.requirement_id = ? AND enroll.section_id = section.section_id AND section.class_id = class.class_id AND " +
									 "class.course_number = concentration_course.course_number AND concentration_course.concentration_id = concentration.concentration_id AND " +
									 "enroll.grade IN ('A+','A','A-','B+','B','B-','C+','C','C-','D','S') GROUP BY topic");
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setInt(2, Integer.parseInt(request.getParameter("requirement_id")));
							ResultSet rs = pstmt.executeQuery();
							CachedRowSet cachedConcentrationUnits = new CachedRowSetImpl();
							cachedConcentrationUnits.populate(rs);
							//System.out.println("----------------------------------");
							// find out the average GPA of each concentration topic taken from the student
							pstmt = conn.prepareStatement("SELECT topic, sum(number_grade*units)/sum(units) AS average FROM enroll, section, class, concentration, concentration_course,grade_conversion " +
									 "WHERE student_id = ? AND concentration.requirement_id = ? AND enroll.section_id = section.section_id AND section.class_id = class.class_id AND " +
									 "class.course_number = concentration_course.course_number AND concentration_course.concentration_id = concentration.concentration_id AND " +
									 "enroll.grade = letter_grade AND enroll.grade_type = 'letter' GROUP BY topic");
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setInt(2, Integer.parseInt(request.getParameter("requirement_id")));
							rs = pstmt.executeQuery();
							CachedRowSet cachedConcentrationGrade = new CachedRowSetImpl();
							cachedConcentrationGrade.populate(rs);
							//System.out.println("----------------------------------");
							completedConcentration = new ArrayList<String>();     //store the completed concentration
							avgGradeSatisfactory = new HashMap<String, Boolean>();
							for (String topic : gradeRequirements.keySet())
								avgGradeSatisfactory.put(topic, false);
							while (cachedConcentrationGrade.next()){        //set the grade flag for each topic
									if (gradeRequirements.get(cachedConcentrationGrade.getString("topic")) <= cachedConcentrationGrade.getFloat("average"))
										avgGradeSatisfactory.put(cachedConcentrationGrade.getString("topic"), true);		
							}
							while (cachedConcentrationUnits.next()){   //test if min_units requirement is met, if so then store the result
								if (unitRequirements.get(cachedConcentrationUnits.getString("topic")) <= cachedConcentrationUnits.getInt("sum"))
									if(avgGradeSatisfactory.get(cachedConcentrationUnits.getString("topic")) == true)
										completedConcentration.add(cachedConcentrationUnits.getString("topic"));
							}
							//System.out.println("----------------------------------");
							//*********************code below deals with remaining courses**************************** //
							
							//find out the courses for each concentration topic 
							courseRemaining = new HashMap<String,LinkedList<String>>();  //store the courses not taken
							pstmt = conn
									.prepareStatement("SELECT concentration_id, topic FROM concentration WHERE requirement_id = ?");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id"))); 
							ResultSet rsConcentrationTopic = pstmt.executeQuery();
							CachedRowSet cachedConcentrationTopic = new CachedRowSetImpl();
							cachedConcentrationTopic.populate(rsConcentrationTopic);
							while (cachedConcentrationTopic.next()){
								pstmt = conn
										.prepareStatement("SELECT course_number FROM concentration_course WHERE concentration_id = ? " +
								"AND course_number NOT IN (SELECT course_number FROM enroll, section, class WHERE student_id = ? " +
								"AND enroll.section_id = section.section_id AND section.class_id = class.class_id)");
								pstmt.setInt(1, cachedConcentrationTopic.getInt("concentration_id"));
								pstmt.setString(2, request.getParameter("student_id"));
								ResultSet rsConcentrationCourse = pstmt.executeQuery();
								LinkedList<String> concentrationCourse = new LinkedList<String>();
								while(rsConcentrationCourse.next()){
									concentrationCourse.add(rsConcentrationCourse.getString("course_number"));
								}
								courseRemaining.put(cachedConcentrationTopic.getString("topic"), concentrationCourse);
							}
							
							conn.setAutoCommit(true);
						}
			
						// Create the statement
						Statement statement = conn.createStatement();
						// Get current quarter.
						InputStream in = application.getResourceAsStream("settings.properties");
						Properties p = new Properties();
						p.load(in);
						String quarter = p.getProperty("quarter");
						int year = Integer.parseInt(p.getProperty("year"));
						
						ResultSet rsStudent = statement.executeQuery("SELECT ssn, student.student_id, first_name, middle_name, last_name FROM student, student_enrollment, ms_student " +
								"WHERE student.student_id = student_enrollment.student_id AND student_enrollment.quarter = '" + quarter + "' AND student_enrollment.year = " + year +
								" AND student.student_id = ms_student.student_id");
						CachedRowSet cachedStudent = new CachedRowSetImpl();
						cachedStudent.populate(rsStudent);
						
						ResultSet rsDegree = statement.executeQuery("SELECT * FROM degree_requirement WHERE title = 'MS'");
						CachedRowSet cachedDegree = new CachedRowSetImpl();
						cachedDegree.populate(rsDegree);

				%>
				<table border="1">
					<tr>
						<form action="report1e.jsp" method="POST">
							<input type="hidden" value="submit" name="action">
							<th>
								<select name="student_id" style="width:200px">
								<%
								String student_id = request.getParameter("student_id");
								while(cachedStudent.next()){
								%>
									<option value=<%=cachedStudent.getString("student_id")%> 
									<%if(student_id !=null && student_id.equals(cachedStudent.getString("student_id"))) out.println("selected = selected");%> >
									<%=cachedStudent.getString("student_id")%> 
									<%=cachedStudent.getString("first_name")%> 
									<%=cachedStudent.getString("middle_name")%> 
									<%=cachedStudent.getString("last_name")%> 
									SSN:<%=cachedStudent.getString("ssn")%> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							
							<th>
								<select name="requirement_id" style="width:200px">
								<%
								int requirement_id = 0 ;
								if(request.getParameter("requirement_id")!=null)
								 	requirement_id = Integer.parseInt(request.getParameter("requirement_id"));
								while(cachedDegree.next()){
								%>
									<option value=<%=cachedDegree.getInt("requirement_id")%> 
									<%if(requirement_id ==cachedDegree.getInt("requirement_id")) out.println("selected = selected");%> >
									<%=cachedDegree.getString("title")%> 
									<%=cachedDegree.getString("department")%> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							
							<th><input type="submit" value="submit"></th>
						</form>
					</tr>
					<%if(completedConcentration!=null){ %>
					<tr>
						<td>Completed Concentration</td>
						<td>
							<ul>
								<%for(String topic: completedConcentration){ %>
									<li><%=topic%></li>
								<%} %>
							</ul>
						</td>
					</tr>
					
					<tr>
						<td>Courses Not Taken</td>
						<td>
							<table border="1">
								<tr>
									<th>Topic</th>
									<th>Course Number : Next Time Offered </th>
									
								</tr>
								<%for (String topic: courseRemaining.keySet()){ %>
								<tr>
									<td><%=topic%></td>
									<td>
										<ul>
									 <%for(String courseNumber : courseRemaining.get(topic)){%>
										
					
										<%
											//fine the next course offered
											PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM class WHERE course_number = ?");  
											pstmt.setString(1, courseNumber);
											ResultSet rs = pstmt.executeQuery();
											String nearQuarter = null;
											int nearYear = Integer.MAX_VALUE;
											while(rs.next()){
												if ((rs.getInt("year") >= year && rs.getInt("year") < nearYear)){
													if (!(rs.getInt("year") ==year && rs.getString("quarter").equals(quarter))){
														nearYear = rs.getInt("year");
														nearQuarter = rs.getString("quarter");
													}
												}
												if(rs.getInt("year") == nearYear){
													if(rs.getString("quarter").equals("Fall") || 
														(rs.getString("quarter").equals("Summer") && nearQuarter.equals("Spring")) ||
														(rs.getString("quarter").equals("Spring") && !nearQuarter.equals(null))){
														nearQuarter = rs.getString("quarter");
													}
												}
											}
											%>
											
										    <li><%=courseNumber%> : <%=(nearYear<Integer.MAX_VALUE)?nearQuarter:"No next class found"%> 
										      <%=(nearYear<Integer.MAX_VALUE)?nearYear:""%></li>
										
									<% 
									}
									%>
										</ul>
									</td>
										
								<%} %>
								</table>
						</td>
					</tr>
					<%} %>
							</table>
						</td>
					</tr>
					
					
						
				<% 
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>